const Router = require('koa-router');
const db = require('../db');
const router = new Router({ prefix: '/api/report' });

router.get('/overview', async (ctx) => {
  try {
    // 从请求参数中获取用户ID，如果未提供则默认为1（用于开发测试）
    const userId = ctx.query.userId || 1;

    // 获取最近30天的课程数据
    const courses = await db.query(`
      SELECT 
        DATE_FORMAT(grouped_date, '%m-%d') as date,
        course_count,
        count
      FROM (
        SELECT 
          DATE(CONVERT_TZ(b.booking_date, '+00:00', '+08:00')) as grouped_date,
          COUNT(DISTINCT b.course_id) as course_count,
          COUNT(*) as count
        FROM bookings b
        WHERE b.user_id = ? 
          AND b.booking_date >= DATE_SUB(NOW(), INTERVAL 30 DAY)
          AND b.status = 'completed'
        GROUP BY DATE(CONVERT_TZ(b.booking_date, '+00:00', '+08:00'))
      ) as daily_stats
      ORDER BY grouped_date DESC
    `, [userId]);

    // 获取分类统计数据
    const categories = await db.query(`
      SELECT 
        c.name as category,
        COUNT(*) as count,
        ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER(), 1) as percentage
      FROM bookings b
      JOIN courses co ON b.course_id = co.id
      JOIN categories c ON co.category_id = c.id
      WHERE b.user_id = ?
        AND b.status = 'completed'
        AND b.booking_date >= DATE_SUB(NOW(), INTERVAL 30 DAY)
      GROUP BY c.id, c.name
      ORDER BY count DESC
    `, [userId]);

    // 获取日历图数据（最近30天的每日完成课程数）
    const calendarData = await db.query(`
      WITH date_range AS (
        SELECT DATE_SUB(CURDATE(), INTERVAL n DAY) as date
        FROM (
          SELECT 0 as n UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION
          SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION
          SELECT 10 UNION SELECT 11 UNION SELECT 12 UNION SELECT 13 UNION SELECT 14 UNION
          SELECT 15 UNION SELECT 16 UNION SELECT 17 UNION SELECT 18 UNION SELECT 19 UNION
          SELECT 20 UNION SELECT 21 UNION SELECT 22 UNION SELECT 23 UNION SELECT 24 UNION
          SELECT 25 UNION SELECT 26 UNION SELECT 27 UNION SELECT 28 UNION SELECT 29
        ) as nums
        ORDER BY date ASC
      )
      SELECT 
        DATE_FORMAT(d.date, '%Y-%m-%d') as date,
        COALESCE(COUNT(b.id), 0) as value
      FROM date_range d
      LEFT JOIN bookings b ON 
        DATE(CONVERT_TZ(b.booking_date, '+00:00', '+08:00')) = d.date
        AND b.user_id = ?
        AND b.status = 'completed'
      GROUP BY d.date
      ORDER BY d.date ASC
    `, [userId]);

    ctx.body = {
      code: 200,
      data: { 
        courses, 
        categories,
        calendar: calendarData
      }
    };
  } catch (error) {
    console.error('获取报表数据失败:', error);
    ctx.status = 500;
    ctx.body = { code: 500, message: '数据获取失败' };
  }
});

// 添加按日期范围查询日历数据的API接口
router.get('/calendar', async (ctx) => {
  try {
    // 从请求参数中获取用户ID和日期范围
    const userId = ctx.query.userId || 1;
    const startDate = ctx.query.startDate || '';
    const endDate = ctx.query.endDate || '';
    
    // 验证日期格式
    if (!startDate.match(/^\d{4}-\d{2}-\d{2}$/) || !endDate.match(/^\d{4}-\d{2}-\d{2}$/)) {
      ctx.status = 400;
      ctx.body = { code: 400, message: '日期格式无效，请使用YYYY-MM-DD格式' };
      return;
    }
    
    // 查询指定日期范围内的课程数据
    const calendarData = await db.query(`
      WITH date_range AS (
        SELECT 
          DATE_ADD(?, INTERVAL n DAY) as date
        FROM (
          SELECT 0 as n UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION
          SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION
          SELECT 10 UNION SELECT 11 UNION SELECT 12 UNION SELECT 13 UNION SELECT 14 UNION
          SELECT 15 UNION SELECT 16 UNION SELECT 17 UNION SELECT 18 UNION SELECT 19 UNION
          SELECT 20 UNION SELECT 21 UNION SELECT 22 UNION SELECT 23 UNION SELECT 24 UNION
          SELECT 25 UNION SELECT 26 UNION SELECT 27 UNION SELECT 28 UNION SELECT 29
        ) as nums
        WHERE DATE_ADD(?, INTERVAL n DAY) <= ?
        ORDER BY date ASC
      )
      SELECT 
        DATE_FORMAT(d.date, '%Y-%m-%d') as date,
        COALESCE(COUNT(b.id), 0) as value
      FROM date_range d
      LEFT JOIN bookings b ON 
        DATE(CONVERT_TZ(b.booking_date, '+00:00', '+08:00')) = d.date
        AND b.user_id = ?
        AND b.status = 'completed'
      GROUP BY d.date
      ORDER BY d.date ASC
    `, [startDate, startDate, endDate, userId]);
    
    ctx.body = {
      code: 200,
      data: { 
        calendar: calendarData
      }
    };
  } catch (error) {
    console.error('获取日历数据失败:', error);
    ctx.status = 500;
    ctx.body = { code: 500, message: '数据获取失败' };
  }
});

module.exports = router;